package dao.impl;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;
import bean.Pagination;
import dao.BookDAO;
import entity.Book;
import entity.Category;
import entity.Product;

public class HibernateBookDAO extends HibernateDaoSupport implements BookDAO{

	@Override
	public Book findBookById(int id) throws Exception {
		return (Book) getHibernateTemplate().get(Book.class, id);
	}

	@Override
	public List<Book> findById(int id, Pagination p) throws Exception {
		return null;
	}

	@Override
	public List<Book> findById(int id) throws Exception {
		String hql ="from Category c join fetch c.pros where c.id=?";
		List<Book> list = getHibernateTemplate().find(hql,id);
		return list;
	}

	@Override
	public List<Book> findByProperty(final String propertyName, final String order,final int id,
			final Pagination p) throws Exception {
		List list = (List) getHibernateTemplate().execute(new HibernateCallback() {
			
			@Override
			public Object doInHibernate(Session session) throws HibernateException,
					SQLException {
				String sql="select * from d_category_product a join d_book b join d_product c on b.id=c.id and a.product_id=b.id and a.cat_id=? order by "+propertyName+" "+order+" limit ?,?";
				SQLQuery query = session.createSQLQuery(sql);
				query.setInteger(0, id);
				query.setInteger(1, p.getStartRow());
				query.setInteger(2, p.getPageSize());
				query.addEntity(Book.class);
				return query.list();
			}
		});

//		Session session = HibernateSessionFactory.getSession();
//		String sql="select * from d_category_product a join d_book b join d_product c on b.id=c.id and a.product_id=b.id and a.cat_id=? order by "+propertyName+" "+order+" limit ?,?";
//		SQLQuery query = session.createSQLQuery(sql);
//		query.setInteger(0, id);
//		query.setInteger(1, p.getStartRow());
//		query.setInteger(2, p.getPageSize());
//		query.addEntity(Book.class);
//		List<Book> list=query.list();
		return list;
	}

	@Override
	public List<Product> findHot(final int size) throws Exception {
		List list = (List)getHibernateTemplate().execute(new HibernateCallback() {
			
			@Override
			public Object doInHibernate(Session session) throws HibernateException,
					SQLException {
				String sql = "select p.* from d_item i join d_product p on i.product_id=p.id where has_deleted=0 group by i.product_id order by sum(i.product_num) desc limit ?";
				SQLQuery query = session.createSQLQuery(sql);
				query.setInteger(0, size);
				List<Object[]> list=query.list();
				List<Product> list2 = new ArrayList<Product>();
				for(Object[] objs:list){
					Product pro = new Product();
					pro.setId(Integer.parseInt(objs[0].toString()));
					pro.setProductName(objs[1].toString());
					pro.setDescription(objs[2].toString());
					pro.setAddTime(Long.parseLong(objs[3].toString()));
					pro.setFixedPrice(Double.parseDouble(objs[4].toString()));
					pro.setDangPrice(Double.parseDouble(objs[5].toString()));
					pro.setKeywords(objs[6].toString());
					pro.setHasDeleted(Integer.parseInt(objs[7].toString()));
					pro.setProductPic(objs[8].toString());
					list2.add(pro);
				}
				return list2;
			}
		});
		return list;
	}

	@Override
	public List<Product> findNew() throws Exception {
		List list =(List) getHibernateTemplate().execute(new HibernateCallback() {
			
			@Override
			public Object doInHibernate(Session session) throws HibernateException,
					SQLException {
				String hql = "from Product where hasDeleted=0 order by addTime desc";
				Query query = session.createQuery(hql);
				query.setFirstResult(0);
				query.setMaxResults(4);
				return query.list();
			}
		});
		return list;
	}

	@Override
	public Product findProById(int id) throws Exception {
		return (Product) getHibernateTemplate().get(Product.class, id);
	}

	@Override
	public List<Book> findRecommend() throws Exception {
		List list =(List) getHibernateTemplate().execute(new HibernateCallback() {
			
			@Override
			public Object doInHibernate(Session session) throws HibernateException,
					SQLException {
				String hql="from Book where hasDeleted=0 order by addTime desc";
				Query query = session.createQuery(hql);
				query.setFirstResult(0);
				query.setMaxResults(2);
				return query.list();
			}
		});
		return list;
	}
}
